﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Odbc;

/// <summary>
/// Summary description for IProfileDAO
/// </summary>
public class MySQLProfileDAOImpl : AbstractDAO, IProfileDAO {
	public MySQLProfileDAOImpl(AbstractDAOFactory factory) : base(factory) {}

	public void saveAccount(User user) {
		if (user.Id > 0) {
			this.updateAccount(user);
		} else {
			this.createAccount(user);
		}
	}

	protected void createAccount(User user) {
		StringBuilder sql = new StringBuilder();
		sql.Append("INSERT INTO User (firstName, middleName, lastName, email, password, securityAnswer_Pet, enabled)");
		sql.Append(" VALUES (?, ?, ?, ?, ?, ?, ?)");

		using (OdbcConnection conn = (OdbcConnection) this.factory.getConnection()) {
			conn.Open();

			OdbcCommand cmd = conn.CreateCommand();
			cmd.CommandText = sql.ToString();
			cmd.Parameters.Add(new OdbcParameter("@firstName", user.FirstName));
			cmd.Parameters.Add(new OdbcParameter("@middleName", user.MiddleName));
			cmd.Parameters.Add(new OdbcParameter("@lastName", user.LastName));
			cmd.Parameters.Add(new OdbcParameter("@email", user.Email));
			cmd.Parameters.Add(new OdbcParameter("@password", user.Password));
			cmd.Parameters.Add(new OdbcParameter("@securityAnswer_Pet", user.SecurityAnswer_Pet));
			cmd.Parameters.Add(new OdbcParameter("@enabled", user.Enabled));
            cmd.ExecuteNonQuery();
			cmd.Dispose();

			//Query updated ID
			OdbcCommand idCmd = conn.CreateCommand();
			idCmd.CommandText = "SELECT @@IDENTITY";
			user.Id = (int) idCmd.ExecuteScalar();
		}
	}

	protected void updateAccount(User user) {
		StringBuilder sql = new StringBuilder();
		sql.Append("UPDATE User SET firstName=?, middleName=?, lastName=?, email=?, securityAnswer_Pet=?, enabled=?");
		if ((user.Password != null) && (user.Password.Length > 0)) {
			sql.Append(", password=?");
		}
		sql.Append(" WHERE ID=?");

		using (OdbcConnection conn = (OdbcConnection) this.factory.getConnection()) {
			conn.Open();

			OdbcCommand cmd = conn.CreateCommand();
			cmd.CommandText = sql.ToString();
			cmd.Parameters.Add(new OdbcParameter("@firstName", user.FirstName));
			cmd.Parameters.Add(new OdbcParameter("@middleName", user.MiddleName));
			cmd.Parameters.Add(new OdbcParameter("@lastName", user.LastName));
			cmd.Parameters.Add(new OdbcParameter("@email", user.Email));
			cmd.Parameters.Add(new OdbcParameter("@securityAnswer_Pet", user.SecurityAnswer_Pet));
			cmd.Parameters.Add(new OdbcParameter("@enabled", user.Enabled));
			if ((user.Password != null) && (user.Password.Length > 0)) {
				cmd.Parameters.Add(new OdbcParameter("@password", user.Password));
			}
			cmd.Parameters.Add(new OdbcParameter("@ID",  user.Id));
			cmd.ExecuteNonQuery();
			cmd.Dispose();
		}
	}

	public List<User> readAccounts(UserSearchCriteria criteria) {
		List<User> result = new List<User>();

		StringBuilder sql_select = new StringBuilder();
		StringBuilder sql_from = new StringBuilder();
		StringBuilder sql_where = new StringBuilder();

		sql_select.Append("SELECT ID, firstName, middleName, lastName, email, securityAnswer_Pet, enabled");

		if (criteria.PopulatePassword) {
			sql_select.Append(", password");
		}

		sql_from.Append(" FROM User");
		sql_where.Append(" WHERE 0=0");

		try {
			using (OdbcConnection conn = (OdbcConnection) this.factory.getConnection()) {
				conn.Open();

				OdbcCommand cmd = conn.CreateCommand();

				if ((criteria.Emails != null) && (criteria.Emails.Count > 0)) {
					sql_where.Append(" AND email IN ('");
					for (int i = 0; i < criteria.Emails.Count; i++) {
						sql_where.Append(criteria.Emails[i]);
						sql_where.Append("'");
						if (i < (criteria.Emails.Count - 1)) {
							sql_where.Append(", ");
						}
					}
					sql_where.Append(")");
				}

				cmd.CommandText = sql_select.ToString() + sql_from.ToString() + sql_where.ToString();

				OdbcDataReader rs = cmd.ExecuteReader();
				while (rs.Read()) {
					//Attempt to use a passed in user object, otherwise create a new one
					int User_ID = (int) rs["ID"];
					User user = null;
					if ((criteria.Ids != null) && (criteria.Ids.Keys.Contains(User_ID))) {
						user = criteria.Ids[User_ID];
					}
					if (user == null) {
						user = new User();
					}

					user.Id = User_ID;
					user.FirstName = (string) rs["firstName"];
					user.MiddleName = (string) rs["middleName"];
					user.LastName = (string) rs["lastName"];
					user.Email = (string) rs["email"];
					user.Enabled = (bool) rs["enabled"];
					user.SecurityAnswer_Pet = (string) rs["securityAnswer_Pet"];

					if (criteria.PopulatePassword) {
						user.Password = (string) rs["password"];
					}

					result.Add(user);
				}
				rs.Close();
			}
		} catch (Exception e) {
			throw new PersistenceException("Failed to read user accounts", e);
		}

		return result;
	}
}
